package com.github.McPlugin.sql;

import com.github.McPlugin.main;
import com.github.McPlugin.util.Goods;
import org.bukkit.command.CommandSender;
import org.bukkit.entity.Player;

import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;

public class SQLiteManager {
    private static SQLiteManager instance = null;
    private static Connection connection = null;
    private static String dataBaseName = null;

    public static SQLiteManager get(){
        return instance==null?instance = new SQLiteManager():instance;
    }


    //连接数据库，可以从config中配置
    public void connectSQLiteDB(){
        try {
            //连接sqlite数据库
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection("jdbc:sqlite:"+"attendance"+".db");
//            connection = DriverManager.getConnection("jdbc:sqlite:"+"store"+".db");
            //创建初始表
            String sql = SQLiteCommands.CREATE_attendance.commandToString();
            String sql_goods = SQLiteCommands.CREATE_goods.commandToString();
            String sql_sell = SQLiteCommands.CREATE_sell.commandToString();
            PreparedStatement ps_goods = connection.prepareStatement(sql_goods);
            PreparedStatement ps_sell = connection.prepareStatement(sql_sell);
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.executeUpdate();
            ps_goods.executeUpdate();
            ps_sell.executeUpdate();
            ps.close();
            ps_goods.close();
            ps_sell.close();
            main.plugin.getLogger().info("连接数据库成功");
        } catch (Exception e) {
            main.plugin.getLogger().info(e.getMessage());
        }
    }

    //关闭数据库的连接
    public void shutSQLiteDB(){
        try {
            main.plugin.getLogger().info("关闭数据成功");
            connection.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    //数据库的操作，操作语句可以写在SQLStatement类中
    //签到
    //初始化玩家信息，即开始时插入玩家信息
    public void insertPlayerAttendance(Player player){
        try {
            String s = SQLiteCommands.ADD_attendance.commandToString();
            PreparedStatement ps = connection.prepareStatement(s);
            ps.setString(1,player.getUniqueId().toString());
            ps.setInt(2,0);
            ps.setString(3, "0000000000000000000000000000000");
            ps.execute();
            ps.close();
            main.plugin.getLogger().info("成功插入玩家信息");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //查询玩家签到信息
    public ArrayList<String> queryPlayerAttendance(Player player) {
        ArrayList<String> strings = new ArrayList<>();
        try {
            String s = SQLiteCommands.SELECT_attendance.commandToString();
            PreparedStatement ps = connection.prepareStatement(s);
            ps.setString(1, player.getUniqueId().toString());
            ResultSet resultSet = ps.executeQuery();
            if(resultSet.next()){
                main.plugin.getLogger().info("数据库拥有数据");
                strings.add(resultSet.getString("TotalTime"));
                strings.add(resultSet.getString("MonthRecord"));
            }
            ps.close();
            main.plugin.getLogger().info("成功获取玩家信息");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return strings;
    }
    //更新玩家签到信息
    public void updatePlayerAttendance(Player player, int totalTime, String monthRecord) {
        try {
            String s = SQLiteCommands.UPDATE_attendance.commandToString();
            PreparedStatement ps = connection.prepareStatement(s);
            ps.setString(1,player.getUniqueId().toString());
            ps.setInt(2,totalTime);
            ps.setString(3, monthRecord);
            ps.setString(4, player.getUniqueId().toString());
            ps.executeUpdate();
            ps.close();
            main.plugin.getLogger().info("成功更新玩家信息");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //商店表中加入数据
    public void insertGoods(int ID,String Name,int Number,int Price,String Type) {
        try {
            String cmd = SQLiteCommands.ADD_goods.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setInt(1, ID);
            preparedStatement.setString(2,Name);
            preparedStatement.setInt(3,Number);
            preparedStatement.setInt(4,Price);
            preparedStatement.setString(5,Type);
            preparedStatement.execute();
            preparedStatement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //查询商店的信息
    public ArrayList<Goods> selectGoods(){
        ArrayList<Goods> goods = new ArrayList<>();
        try {
            String cmd = SQLiteCommands.SELECT_goods.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            ResultSet resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                Goods good = new Goods();
                good.setId(resultSet.getInt("ID"));
                good.setName(resultSet.getString("Name"));
                good.setNumber(resultSet.getInt("Number"));
                good.setPrice(resultSet.getInt("Price"));
                good.setType(resultSet.getString("Type"));
                goods.add(good);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goods;
    }

    //更新商店的信息
    public void updateGoods(int id, int number){
        try {
            String cmd = SQLiteCommands.UPDATE_goods.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setInt(1,number);
            preparedStatement.setInt(2,id);
            preparedStatement.executeUpdate();
            preparedStatement.close();
            main.plugin.getLogger().info("更新成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //向销售记录表中添加消息
    public void insertSell(int ID,String UUID,String Name,int Number,int Price) {
        try {
            String cmd = SQLiteCommands.ADD_sell.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setInt(1, ID);
            preparedStatement.setString(2,UUID);
            preparedStatement.setString(3,Name);
            preparedStatement.setInt(4,Number);
            preparedStatement.setInt(5,Price);
            preparedStatement.execute();
            preparedStatement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


}
